package com.example.erp.repository;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.example.erp.entity.Book;

/**
 * 图书服务数据层实现.
 */
@Repository
public class BookRepository {
	@Autowired
	private NamedParameterJdbcTemplate nameDao;

	/**
	 * 根据书名模糊查询.
	 * @param title
	 * @return
	 */
	public List<Book> findByTitleContaining(String title) {
		String sql = "SELECT id,title,category,author,publicationDate,isbn FROM t_library WHERE title LIKE :title";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("title", "%" + title + "%");
		// BeanPropertyRowMapper 是 Spring 提供的一个通用 RowMapper 实现，
		// 它会自动将数据库查询结果的每一列（如 id, title 等）映射到 Book 类的同名属性上。
		RowMapper<Book> bookRowMapper = new BeanPropertyRowMapper<>(Book.class);
		List<Book> results = nameDao.query(sql, params, bookRowMapper);
		return results;
	}

	/**
	 * 根据作者查询.
	 * @param author
	 * @return
	 */
	public List<Book> findByAuthor(String author) {
		String sql = """
				SELECT id,title,category,author,publicationDate,isbn
				  FROM t_library
				 WHERE author LIKE :author
				""";	
		
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("author", "%" + author + "%");
		// BeanPropertyRowMapper 是 Spring 提供的一个通用 RowMapper 实现，
		// 它会自动将数据库查询结果的每一列（如 id, title 等）映射到 Book 类的同名属性上。
		RowMapper<Book> bookRowMapper = new BeanPropertyRowMapper<>(Book.class);
		List<Book> results = nameDao.query(sql, params, bookRowMapper);
		return results;
	}

	/**
	 * 根据分类查询.
	 * @param category
	 * @return
	 */
	public List<Book> findByCategory(String category) {
		String sql = """
				SELECT id,title,category,author,publicationDate,isbn
				  FROM t_library
				 WHERE category LIKE :category
				""";
		
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("category", "%" + category + "%");
		// 匿名函数方式实现行数据转为book对象。
		List<Book> results = nameDao.query(sql, params,
				(resultSet, rowNum) -> new Book(resultSet.getLong("id"), 
												resultSet.getString("title"),
												resultSet.getString("category"), 
												resultSet.getString("author"),
												resultSet.getDate("publicationDate").toLocalDate(), 
												resultSet.getString("isbn")
										));
		return results;
	}
	
	/**
	 * 列出所有图书信息.
	 * @return
	 */
	public List<Book> findAllBooks() {
		String sql = """
					SELECT id,title,category,author,publicationDate,isbn
					  FROM t_library
					""";
		MapSqlParameterSource params = new MapSqlParameterSource();
		// 匿名函数方式实现行数据转为book对象。
		List<Book> results = nameDao.query(sql, params,
				(resultSet, rowNum) -> new Book(resultSet.getLong("id"), 
												resultSet.getString("title"),
												resultSet.getString("category"), 
												resultSet.getString("author"),
												resultSet.getDate("publicationDate").toLocalDate(), 
												resultSet.getString("isbn")
										));
		return results;
	}
	
	/**
	 * 列出图书数量.
	 * @return
	 */
	public int countBook() {
		String sql = "SELECT count(*) FROM t_library";
		MapSqlParameterSource params = new MapSqlParameterSource();
		int results = nameDao.queryForObject(sql, params, Integer.class);
		return results;
	}
	
	/**
	 * 新增图书信息.
	 * 
	 * @param book
	 * @return
	 */
	public int addBook(Book book) {
		String sql = """
					INSERT INTO t_library (id,title,category,author,publicationDate,isbn )
					      VALUES (:id,:title,:category,:author,:publicationDate,:isbn)
					""";
		
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("id", book.getId());
		params.addValue("title", book.getTitle());
		params.addValue("category",book.getCategory());
		params.addValue("author", book.getAuthor());
		params.addValue("publicationDate",book.getPublicationDate());
		params.addValue("isbn", book.getIsbn());
		int result = nameDao.update(sql, params);
		return result;
	}

	/**
	 * 更新图书信息.
	 * 
	 * @param book
	 * @return
	 */
	public int updateBook(Book book) {
		String sql = """
					UPDATE t_library 
					   SET title=:title,category=:category,author=:author,publicationDate=:publicationDate,isbn=:isbn
					 WHERE id=:id
					""";
		
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("title", book.getTitle());
		params.addValue("category", book.getCategory());
		params.addValue("author", book.getAuthor());
		params.addValue("publicationDate", book.getPublicationDate());
		params.addValue("isbn", book.getIsbn());
		
		params.addValue("id", book.getId());
		int result = nameDao.update(sql, params);
		return result;
	}

	/**
	 * 删除图书信息.
	 * 
	 * @param book
	 * @return
	 */
	public int deleteBook(Book book) {
		String sql = "DELETE FROM t_library WHERE id=:id";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("id", book.getId());
		int result = nameDao.update(sql, params);
		return result;
	}
}
